package com.example.androidmainshixun.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.example.androidmainshixun.entity.PersonalInfo;
import com.example.androidmainshixun.entity.User;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "NYB";// 数据库名
    private static final int DATABASE_VERSION = 1;// 数据库版本号
    private static final String TABLE_NAME_GOODS = "goods"; //商品表
    private static final String TABLE_NAME_USER = "user"; //用户表
    private String COLUMN_USER_PASS = "pass";
    private String COLUMN_ID = "user_id";
    private String COLUMN_USER_NAME = "username";
    private static final String TABLE_NAME_NEWS = "news";  //新闻表
    private static final String TABLE_NAME_ORDER = "order2"; // 订单表 order是关键字
    private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
    private static final String TABLE_NAME_PHOTO = "photo";//图片表
    private static final String TABLE_NAME_MESSAGE = "messages";//消息表
    private static final String TABLE_NAME_PERSONALCENTER = "personalcenter";//个人信息表
    private static DBHelper mHelper = null;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static DBHelper getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new DBHelper(context);
        }
        return mHelper;
    }

    //数据库创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }

    //数据库升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
        }
    }

    private void initTable(SQLiteDatabase db) {
        // 创建商品表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
                "_id integer primary key autoincrement," +
                "goods_name text," +
                "goods_price double," +
                "goods_unit text," +
                "goods_browse integer," +
                "goods_message text)");
        // 创建用户表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
                "_id integer primary key autoincrement," +
                "username text," +
                "pass text)");
        // 创建订单表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
                "_id integer primary key autoincrement," +
                "order_name text," +
                "order_number  bigint," +
                "user_id  integer," +
                "FOREIGN KEY (user_id) REFERENCES user(_id))");
        // 创建图片表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
                "_id integer primary key autoincrement," +
                "goods_id  integer," +
                "image BLOB," +
                "FOREIGN KEY (goods_id) REFERENCES goods(_id))");
        // 创建个人信息表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PERSONALCENTER + " (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT," +
                "number TEXT," +
                "age INTEGER," +
                "address TEXT," +
                "userid  INTEGER," +
                "FOREIGN KEY (userid) REFERENCES user(_id))");
        //创建消息表
        db.execSQL("CREATE TABLE messages ("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "sender_id TEXT,"
                + "receiver_id TEXT,"
                + "content TEXT,"
                + "timestamp INTEGER"
                + ");");
    }

    private void initData(SQLiteDatabase db) {
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('黄瓜', 1.23, '斤', '123', '来自四川雪山之巅的黄瓜。')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('东北大米', 3.33, '斤', '444', '来自东北的大米，煮饭特别香。')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('海南香蕉', 9.99, '斤', '213', '海南香蕉，特别好吃')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('新疆哈密瓜', 5.99, '斤', '333', '又大又甜，特别好吃')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('江苏黄瓜', 1.12, '斤', '223', '来自江苏的黄瓜。')");

        db.execSQL("insert into " + TABLE_NAME_USER +
                " (username , pass) " +
                "values" +
                "('222', '222')");
        db.execSQL("insert into " + TABLE_NAME_USER +
                " (username , pass) " +
                "values" +
                "('333', '333')");

        db.execSQL("insert into " + TABLE_NAME_PERSONALCENTER +
                " (username , number,age,address,userid) " +
                "values" +
                "('222', '156452367',18,'吉林市',1)");
        db.execSQL("insert into " + TABLE_NAME_PERSONALCENTER +
                " (username , number,age,address,userid) " +
                "values" +
                "('333', '178564948',26,'黑龙江市',2)");

        db.execSQL("insert into " + TABLE_NAME_MESSAGE +
                " (receiver_id,content,timestamp) " +
                "values" +
                "('444', '你好','13:39')");

    }

    //搜索方法
    public List<String> searchGoods(String query) {
        SQLiteDatabase db = this.getReadableDatabase();
        List<String> goodsList = new ArrayList<>(); // 存储搜索结果的列表
        String[] columns = {"goods_name","goods_price","goods_unit","goods_browse","goods_message"}; // 假设我们只搜索商品名称

        String selection = "goods_name LIKE ?";
        String[] selectionArgs = {"%" + query + "%"}; // 构建搜索模式

        Cursor cursor = db.query(TABLE_NAME_GOODS, columns, selection, selectionArgs, null, null, null);

        while (cursor.moveToNext()) {
            @SuppressLint("Range")
            String goodsName = cursor.getString(cursor.getColumnIndex("goods_name"));
            @SuppressLint("Range")
            String goodsPrice = cursor.getString(cursor.getColumnIndex("goods_price"));
            @SuppressLint("Range")
            String goodsUnit = cursor.getString(cursor.getColumnIndex("goods_unit"));
            @SuppressLint("Range")
            String goodsBrowse = cursor.getString(cursor.getColumnIndex("goods_browse"));
            @SuppressLint("Range")
            String goodsMessage = cursor.getString(cursor.getColumnIndex("goods_message"));
            goodsList.add(goodsName);
            goodsList.add(goodsPrice);
            goodsList.add(goodsUnit);
            goodsList.add(goodsBrowse);
            goodsList.add(goodsMessage);
            // 添加商品名称到列表
        }
        cursor.close();
        db.close();
        return goodsList;
    }

    /*修改密码*/
    public int UpdatePass(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_PASS, user.getPass());
        String whereClause = COLUMN_USER_NAME + " = ?";
        String[] whereArgs = {String.valueOf(user.getUsername())};
        int count = db.update(TABLE_NAME_USER, values, whereClause, whereArgs);
        return count;
    }

    public int insertuser(User user) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", user.getUsername());
        values.put("pass", user.getPass());
        long newRowID = db.insert(TABLE_NAME_USER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }

    @SuppressLint("Range")
    public User login(String username, String pass) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME_USER,                                // 表名
                null,                                           // 要查询的列数据
                "(username = ?) and pass = ?",                  // 条件语句
                new String[]{username, pass},                  // 条件语句的值
                null,                                           // group by
                null,                                           // having
                null                                            // 排序语句
        );
        User user = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                user = new User();
                user.setUserid(cursor.getInt(cursor.getColumnIndex("_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setPass(cursor.getString(cursor.getColumnIndex("pass")));
                Log.d(TAG, "login = " + user.toString());
            }
        }
        return user;
    }


    /*判断用户是否存在*/
    @SuppressLint("Range")
    public User getUserByName(String username) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME_USER,
                null,
                COLUMN_USER_NAME + " = ?",
                new String[]{username},
                null,
                null,
                null);
        User user = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                user = new User();
                user.setUserid(cursor.getInt(cursor.getColumnIndex("_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            }
        }
        return user;
    }

    //检测登录状态
    @SuppressLint("Range")
    public User getUserById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME_USER,                         // 表名
                null,                                   // 要查询的列数据
                "_id = ?",                              // 条件语句
                new String[]{String.valueOf(id)},      // 条件语句的值
                null,                                   // group by
                null,                                   // having
                null                                    // 排序语句
        );
        User user = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                user = new User();
                user.setUserid(cursor.getInt(cursor.getColumnIndex("_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setPass(cursor.getString(cursor.getColumnIndex("pass")));
                Log.d(TAG, "current student = " + user.toString());
            }
        }
        return user;
    }

    //个人信息
    @SuppressLint("Range")
    public PersonalInfo getPersonalInfoByUserId(int userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        PersonalInfo personalInfo = null;
        // 编写SQL查询，使用用户ID作为条件
        String sql = "SELECT " + TABLE_NAME_USER + "._id, " + TABLE_NAME_USER + ".username, "
                + TABLE_NAME_PERSONALCENTER + ".number, "
                + TABLE_NAME_PERSONALCENTER + ".age, "
                + TABLE_NAME_PERSONALCENTER + ".address "
                + "FROM " + TABLE_NAME_USER
                + " INNER JOIN " + TABLE_NAME_PERSONALCENTER
                + " ON " + TABLE_NAME_USER + "._id = " + TABLE_NAME_PERSONALCENTER + ".userid"  // 确保这里使用正确的字段名
                + " WHERE " + TABLE_NAME_USER + "._id = ?";
        // 将用户ID作为字符串数组传递给rawQuery方法
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(userId)});
        try {
            if (cursor != null && cursor.moveToFirst()) {
                personalInfo = new PersonalInfo();
                personalInfo.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                personalInfo.setNumber(cursor.getString(cursor.getColumnIndex("number")));
                personalInfo.setAge(cursor.getInt(cursor.getColumnIndex("age")));
                personalInfo.setAddress(cursor.getString(cursor.getColumnIndex("address")));
                Log.d(TAG, "Found personalInfo: " + personalInfo.toString());
            }
        } finally {
            if (cursor != null) {
                cursor.close(); // 确保游标在使用后被关闭
            }
        }
        return personalInfo;
    }

    public int insertpersonalInfo1(PersonalInfo personalInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", personalInfo.getUsername());
        long newRowID = db.insert(TABLE_NAME_PERSONALCENTER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }

    public int insertpersonalInfo2(PersonalInfo personalInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("number", personalInfo.getNumber());
        long newRowID = db.insert(TABLE_NAME_PERSONALCENTER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }

    public int insertpersonalInfo3(PersonalInfo personalInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("age", personalInfo.getAge());
        long newRowID = db.insert(TABLE_NAME_PERSONALCENTER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }

    public int insertpersonalInfo4(PersonalInfo personalInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("addess", personalInfo.getAddress());
        long newRowID = db.insert(TABLE_NAME_PERSONALCENTER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }

    public int insertpersonalInfo5(PersonalInfo personalInfo,String userid) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", personalInfo.getUsername());
        values.put("number", personalInfo.getNumber());
        values.put("age", personalInfo.getAge());
        values.put("address", personalInfo.getAddress());
        values.put("userid",personalInfo.getUserid());
        long newRowID = db.insert(TABLE_NAME_PERSONALCENTER, null, values);
        Log.d(TAG, "insertuser newRowID =" + newRowID);
        return (int) newRowID;
    }
    public int getLoggedInUserId(SQLiteDatabase db, String username, String pass) {
        String query = "SELECT _id FROM " + TABLE_NAME_USER + " WHERE username = ? AND pass = ?";
        SQLiteStatement statement = db.compileStatement(query);
        statement.bindString(1, username);
        statement.bindString(2, pass);
        long userid = statement.simpleQueryForLong();
        statement.close();
        return (int) userid;
    }
    // 更新个人信息表的方法
    public int updatePersonalInfo(SQLiteDatabase db, PersonalInfo personalInfo, String userId) {
        // 创建一个ContentValues对象来存储更新的数据
        ContentValues values = new ContentValues();
        values.put("username", personalInfo.getUsername());
        values.put("number", personalInfo.getNumber());
        values.put("age", personalInfo.getAge());
        values.put("address", personalInfo.getAddress());
        // 构建WHERE子句以确定要更新的记录
        String whereClause = "userid = ?";
        String[] whereArgs = new String[]{String.valueOf(personalInfo.getUserid())};
        // 使用SQLiteDatabase的update方法执行更新操作
        int rowsAffected = db.update(TABLE_NAME_PERSONALCENTER, values, whereClause, whereArgs);
        return rowsAffected; // 返回受影响的行数
    }

}